package com.mangguo.tingjian.content.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mangguo.tingjian.content.dto.ContentCountResult;
import com.mangguo.tingjian.content.dto.CountResult;
import com.mangguo.tingjian.content.dto.UvpvCountResult;
import com.mangguo.tingjian.content.entity.DwClick;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * click同步表 Mapper 接口
 * </p>
 *
 * @author auto
 * @since 2019-11-27
 */
@Mapper
public interface DwClickMapper extends BaseMapper<DwClick> {
//    @Select("select count(1) AS uvCount,lvl3 from (select imei,lvl3 from dw_click where mdate >= #{start} and mdate <= #{end} and lvl1 = #{lvl1} and lvl2 = #{lvl2} group by lvl3,imei) as a group by  lvl3 order by uvCount desc limit 20")
    @Select("<script>" +
            "select count(DISTINCT imei) as uvCount,lvl3 from dw_click force index(dw_click_mDate_lvl_IDX) " +
            "where mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            "1=1 " +
            " group by lvl3 order by uvCount desc limit 20" +
            "</script>")
    List<UvpvCountResult> topUvByLvlBetweenDate(@Param("start")Integer start,
                                                @Param("end") Integer end,
                                                @Param("lvl1") Long lvl1,
                                                @Param("lvl2") Long lvl2);


//    @Select("select count(1) AS pvCount from dw_click where mdate >= #{start} and mdate <= #{end} and lvl1 = #{lvl1} and lvl2 = #{lvl2} and lvl3 = #{lvl3}")
    @Select("<script>" +
            "select count(1) AS pvCount  from dw_click  force index(dw_click_mDate_lvl_IDX) where " +
            " mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            " lvl3 = #{lvl3} " +
            "</script>")
    Long countPvByLvl3BetweenDate(@Param("start")Integer start,
                                   @Param("end") Integer end,
                                   @Param("lvl1") Long lvl1,
                                   @Param("lvl2") Long lvl2,
                                   @Param("lvl3") Long lvl3);

    @Select("<script>" +
            "select count(1) AS pvCount,lvl3  from dw_click  force index(dw_click_mDate_lvl_IDX) where " +
            " mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            "<if test='lvl3s != null and lvl3s.size()>0'> "
            + "lvl3 IN "
            + "<foreach item='lvl3' collection='lvl3s' open='(' close=')' separator=','> "
            + "#{lvl3}"
            + "</foreach> AND "
            + "</if>" +
            " 1 = 1 group by lvl3" +
            "</script>")
    List<UvpvCountResult> countPvByLvl3sBetweenDate(@Param("start")Integer start,
                                                  @Param("end") Integer end,
                                                  @Param("lvl1") Long lvl1,
                                                  @Param("lvl2") Long lvl2,
                                                  @Param("lvl3s") List<Long> lvl3s);


//    @Select("select count(1) AS uvCount from (select imei,lvl3 from dw_click where mdate >= #{start} and mdate <= #{end} and lvl1 = #{lvl1} and lvl2 = #{lvl2} and lvl3 = #{lvl3} group by lvl3,imei) as a")
    @Select("<script>" +
            "select count(DISTINCT imei) as uvCount,lvl3 from dw_click force index(dw_click_mDate_lvl_IDX) where  " +
            " mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            " lvl3 = #{lvl3} " +
            " group by lvl3" +
            "</script>")
    Long countUvByLvl3BetweenDate(@Param("start")Integer start,
                                  @Param("end") Integer end,
                                  @Param("lvl1") Long lvl1,
                                  @Param("lvl2") Long lvl2,
                                  @Param("lvl3") Long lvl3);

//    @Select("select count(1) AS pvCount,lvl3 from dw_click where mdate >= #{start} and mdate <= #{end} and lvl1 = #{lvl1} and lvl2 = #{lvl2} group by lvl3 order by pvCount desc limit 20")
    @Select("<script>" +
            "select count(1) AS pvCount,lvl3 from dw_click force index(dw_click_mDate_lvl_IDX)" +
            "where mdate &gt;= #{start} and mdate &lt;= #{end} " +
            "and " +
            "<if test='lvl1 != null and lvl1 != -1L'> " +
            " lvl1 = #{lvl1} and " +
            "</if>" +
            "<if test='lvl2 != null and lvl2 != -1L'> " +
            " lvl2 = #{lvl2} and " +
            "</if>" +
            "1=1 " +
            "group by lvl3 order by pvCount desc limit 20" +
            "</script>")
    List<UvpvCountResult> topPvByLvlBetweenDate(@Param("start")Integer start,
                                               @Param("end") Integer end,
                                               @Param("lvl1") Long lvl1,
                                               @Param("lvl2") Long lvl2);

    @Select("select count(1) from dw_click where mdate >= #{start} and mdate <= #{end} and contentId=#{contentId} and contentType=#{contentType} and dictCode in ('300000000001','300000000002','300000000003')")
    Long countShareByContentIdBetweenDate(@Param("start")Integer start,
                                          @Param("end") Integer end,
                                          @Param("contentId") Long contentId,
                                          @Param("contentType") Integer contentType);

    @Select("select count(1) as count, mdate from dw_click where mdate >= #{start} and mdate <= #{end} and contentId=#{contentId} and contentType=#{contentType} and dictCode in ('300000000001','300000000002','300000000003') group by mdate")
    List<CountResult> countShareByContentIdBetweenDateGroupByDate(@Param("start")Integer start,
                                                                  @Param("end") Integer end,
                                                                  @Param("contentId") Long contentId,
                                                                  @Param("contentType") Integer contentType);

    @Select("<script>select count(1) as count,mdate,contentId " +
            " from dw_click where mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='contentIds != null and contentIds.size()>0'> "+
            "contentId IN " +
            "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "+
            "#{contentId}"+
            "</foreach> AND "+
            "</if>"+
            "contentType = #{contentType} and dictCode in ('300000000001','300000000002','300000000003') " +
            "group by mdate,contentId</script>")
    List<ContentCountResult> countShareByContentIdBetweenDateGroupByDates(@Param("start")Integer start,
                                                                           @Param("end")Integer end,
                                                                           @Param("contentIds") List<Long> contentIds,
                                                                           @Param("contentType") Integer contentType);

    @Select("select count(1) as count,mdate from dw_click where mdate >= #{start} and mdate <= #{end} and contentId = #{contentId} and contentType = 2 group by mdate")
    List<CountResult> countAudioClickTime(@Param("start")Integer start,
                                         @Param("end")Integer end,
                                         @Param("contentId") Long contentId);


    @Select("<script>select count(1) as count,mdate,contentId" +
            " from dw_click where mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='contentIds != null and contentIds.size()>0'> "+
            "contentId IN " +
            "<foreach item='contentId' collection='contentIds' open='(' close=')' separator=','> "+
            "#{contentId}"+
            "</foreach> AND "+
            "</if>"+
            "contentType = 2 " +
            "group by mdate,contentId</script>")
    List<ContentCountResult> countAudioClickTimeByContents(@Param("start")Integer start,
                                                           @Param("end")Integer end,
                                                           @Param("contentIds") List<Long> contentIds);

    @Select("<script>select count(1) as count,mdate,contentId" +
            " from dw_click where mdate &gt;= #{start} and mdate &lt;= #{end} and " +
            "<if test='albumIds != null and albumIds.size()>0'> "+
            "contentId IN " +
            "<foreach item='contentId' collection='albumIds' open='(' close=')' separator=','> "+
            "#{contentId}"+
            "</foreach> AND "+
            "</if>"+
            "contentType = 1 " +
            "group by mdate,contentId</script>")
    List<ContentCountResult> countAlbumClickTimeByContents(@Param("start")Integer start,
                                                           @Param("end")Integer end,
                                                           @Param("albumIds") List<Long> albumIds);
}
